After completing this unit, you will be able to:
A Web Intelligence document consists of three components. What are they? A query filter contains three components. What are they? What object type does not have a list of values? When you list values in a formula, what must you use to separate the values? What are the two benefits provided when you apply a break to a table? What are the differences among a query filter, a report filter, and a block filter? What is the difference between a section and a break? What is the difference between an ascending or descending sort and a custom sort?
Why purge the data in your document?
What is the difference between a dimension and a detail?
You have two queries on two different data sources. Query 1 returns 10 rows for Region. Query 2 returns 12 rows for Region_Area . You synchronize the two data sources by merging the Region and Region_Area objects.
How many rows appear after the synchronization?
You have a report with the objects Region, Year, and Sales revenue in Query 1, and you
have another report on a different data source with Region, Area, and Sales total in Query 2. Which objects do you choose to merge to synchronize the information in one table?
When you synchronize two queries from different data sources, what rules must you respect?
Can you synchronize the data between a Web Intelligence document and an Excel spreadsheet?
After completing this lesson, you are now able to:
Build on your knowledge of the Web Intelligence core reporting concepts reviewed
When you design Web Intelligence documents, there are certain instances where you may need to build two or more queries to retrieve the results you require in your report.
As a Web Intelligence user, there are several techniques available that enable you to do this. They are: Data synchronization using merged dimensions
Data synchronization refers to the process of merging data from multiple sources into a single block in a document.
Note: This technique is presented in the BusinessObjects Web Intelligence XI 3.0/3.1: Report Design course. Subqueries
The subquery technique enables you to specify the output of a query as the operand value(s) for a query filter of another query.
Note: The subquery technique is presented later in this lesson. Combined queries
The combined queries technique enables you to create two queries and merge the results of both into a single data provider on a selective basis. This can be done by using the Union, Intersection, or Minus functions.
Note: In this unit, you will see that combining queries in a single data provider is different from synchronizing multiple data providers using merged dimensions.
This unit describes how to use combined queries.
After completing this unit, you will be able to:
-Describe the Union, Intersection, and Minus functions used to combine queries
-Discuss reasons and advantages to using combined queries instead of applying complex filters
-Create a combined query
-Remove a combined query
-List important facts to remember when using combined queries
Adding single and complex query filters to a query allows you to restrict the amount of information returned by the query. This standard technique allows you to combine multiple filters in a single query, and these combinations can be designed to make queries very specific and limiting.
However, this technique only works with a single query. There may be situations when you want to combine the results of two queries into a single block.
To accomplish this, you must build a combined query. All combined queries are built in the Query panel, and can only be built using a single universe. Each query needs to have the same number of objects as well as the same data types.
There are three methods of combining queries:
Union: combines results which appear in Query 1 OR Query 2.
You use a union query to combine the data from two objects in a single column in a table. Union queries are especially useful for working with incompatible objects.
For example, if you built a query with two incompatible objects, Web Intelligence would run separate SQL statements for each object and then return the data in different blocks (tables). A union query forces Web Intelligence to return the data from both objects together in one column.
Unions, when used to combine the results of two queries using the same objects, will remove duplicate values, leaving a single instance of the duplicate in the report.
Note: Union is the default operator for combined queries.
Intersection: combines results which appear in Query 1 AND Query 2.
You use an intersection query to obtain data common to two sets of results. Like a union query, Web Intelligence considers each select statement separately and combines their results in the end.
Unlike a union query, the intersection query only returns those values that are in both queries. In this sense, it works much the same as using the AND operator when creating a regular query with multiple query filters.
Minus: combine results which include everything in Query 1 except for what is also true in Query
You use a minus query to exclude the results of one query from the main query result (Query 1). For example, a minus query could be used to find out which customers bought product A but not product B.
You could not obtain this data with standard query filters since the result sets need to be obtained separately before being combined. Like a union query, the minus query considers each query separately and combines their results in the end.
When you build minus queries, you must pay attention to the order of the queries, since the results of Query 2 will always be subtracted from the results of Query 1.
Understanding when to use a combined query
The following is a typical scenario where you might choose to use a combined query:
You need to find the dates on which your customers either made reservations or paid their invoices. The query requires two incompatible objects: Invoice Date and Reservation Date. While the data that the objects represent is identical (that is, a date is a date), the intent of the dates is incompatible and therefore, the objects are incompatible.
Since the objects are incompatible, if you include them in a standard query, Web Intelligence returns two blocks of data and does not synchronize the values in a single block.
However, by building a combined query using the Union function, with Invoice Date in the first query and Reservation Date in the other, the data appears in a single column in the block.
Combining queries is similar but not equivalent to Boolean logical operators that you may have used when combining conditions in a single query. The Intersection function is similar in effect to AND; the Union function is similar in effect to OR; the Minus function is similar in effect to NOT.
However, the column of data retrieved by the combined query displays a header that reflects the date object from the first query only. In other words, using the example above, the query returns a column entitled Invoice Date, but the column actually contains both invoice dates and reservation dates. This is important to understand, as it may require you to reformat the column header.
Advantages of using combined queries
There are a number of reasons why you may want to use combined queries instead of applying multiple query filters against a single query:
-To make the construction of the query easier
-When it is not possible to set the required query filters using Boolean logic
The downside of using the combined query technique is that because you are actually creating multiple queries, processing against the database may take longer.
Note: A decision on whether to use query filters or a combined query to retrieve the data you need often depends on how the data is structured in your database.
Comparing query filters and combined queries
Suppose that you have been asked by the Marketing Manager of the eFashion group to produce a report listing all products that have not had a special promotion across all stores.
The resulting report should list 203 products that have not had a special promotion.
Note: The table in this image has been truncated.
To produce this report, you need to resolve issues concerning data integrity.
If you create two queries, one to show the total list of product SKU numbers and SKU descriptions, the other to show the list of SKU numbers, SKU descriptions, and products that have had a promotion (Promotion y/n), and then apply a count on the SKU number column for each query, you will see that while the first query produces a list of 211 records, the second produces a list of only 8.
This is because the eFashion database only has information on product promotions where a promotion has actually occurred.
In this case, of the 211 records available, there are only 8 records where an entry identifies that a promotion has taken place. If you apply a query filter to display only those products where the Promotion y/n value is not equal to Yes, you get a message stating that there is no data to return. You get the same message if you apply a query filter to display only those products where the Promotion y/n value is null.
Due to a relational database limitation, when products are listed with a Promotion column, only products that have a Promotion y/n value will show up in the report. This is a common data integrity issue. You need to use a combined query to generate the results you require.
Using the combined query technique
The combined query technique combines the results of one query with the results of another query into a single query, or data provider. The manner in which the data is combined depends on the function you choose: Union, Minus, or Intersection .
In the previous scenario, you could not generate a table listing only the products for which there were no promotions, because the Promotion y/n object only contains data about the products for which there were promotions. However, you can generate the table you need by creating a combined query using the SKU Number and SKU desc objects in Combined Query 1, and adding the following query filter to Combined Query 2.
The first query produces a list of all products, and the second query produces a list of only those products for which there has been a promotion. By using the Minus function to combine the two queries, you can generate a table that subtracts the 8 records generated by Combined Query 2 from the 211 generated by Combined Query 1, resulting in a table listing the 203 products for which there has been no promotion.
To build a combined query
Create an initial query in the Query panel.
Click Combined Query on the toolbar.
Web Intelligence adds a copy of the initial query to the data provider. The second query has the following characteristics:
-It contains the same objects as the original query.
-It does not contain the filters defined on the original query.
-It is combined with the original query in a Union relationship.
To switch to a query, click Combined Query n.
The individual queries in the combined queries are named Combined Query n.
To delete a query, right-click the Combined Query n you want to delete, then select Remove on the menu.
-To change the combination type, double-click the operator. The operator moves through the sequence Union, Intersection, Minus.
-Build each query within the combined query as you build any normal Web Intelligence query.
-Click Run Query.
Important facts about combined queries
Below are some important facts to keep in mind when you use combined queries:
| • | Queries that you combine must | be built using the same universe. |
| • | Queries that you combine must | contain the same number of objects to run successfully. |
| • | When you build a combined query to return data from more than one object in a column,for example, using the Union operator, you must use objects of the same type (character,date or number). | |
| • | Only the objects placed in the first query are displayed in the block after the query is run.Any object used in a combined query does not display in its own column, but instead, thevalues are returned in the same column as the object from the first query. | |
| • | Web Intelligence's default behavior when combining queries is to perform a Union (not a UnionAll) and to return all values less the duplicates. You need to define a custom formulaor variable for Web Intelligence to perform a UnionAll. | |
Retrieving duplicate or unique rows
In a database, the same data may be repeated over many rows. By default, Web Intelligence automatically returns all rows, even if they are repeated. This default behavior may cause an inaccurate count when combining queries, particularly when you use the Minus operator.
You can change Web Intelligence’s default behavior so that only unique rows are returned by the combined query.
To retrieve only unique rows
1. In the Query panel, display the Query Properties tab. 2. In the Data zone, clear the Retrieve duplicate rows option.
Activity: Combined queries
Objective
Using Web Intelligence Rich Client, create a document by combining queries, using the Union, Intersection or Minus functions.
Instructions
Note: To log onto Web Intelligence Rich Client, you need a user name and password for the BusinessObjects Enterprise server. Refer to your class instructor for this information.
You want to create a query that shows a list of product lines. The list must show:
AND
Using the combined query technique, create the following table:
Which operator will you use to combine the queries?
Activity: Combined queries - Optional
Objective
Instructions
You are interested in reporting on the relationship between the eFashion stores that earned at least $3,000,000 in sales revenue and those stores whose margin was at least $1,300,000.
Assume that you already have two reports built using the following queries:
Report 1:1
Report 2:1
Using the combined query technique, create a table showing all Store names that have both Sales revenue of $3,000,000 or higher and a Margin of $1,300,000 or higher.
• Which operator will you use to combine the queries?
Once you run the combined query, apply a Count to the Store name column. The table should appear like this
Edit the query and had Sales revenue
update the data in the table so that it shows all Store names that have of at least $3,000,000 but not a margin of $1,300,000 or more.
Which operator will you use to combine the queries?
Once you run the combined query, check that the Count is still applied to the Store name column.
The table appears like this:
$3,000,000 or a Margin of at least $1,300,000.
Which operator will you use to combine the queries?
Once you run the combined query, check that the Count is still applied to the Store name column.
The table should appear like this:
7. Which of the previous results could you also achieve using multiple query filters?
8. Save the document as Act_Combinedqueries_Opt.
A subquery, as its name suggests, is a query within a query. It contains an inner query, which returns a set of data that is used as the basis for a second, outer (or main) query.
After completing this unit, you will be able to:
-Describe subqueries
-Create a subquery
Subqueries are used in cases where the results of the main query are dependent upon the results of the inner query. This means that the inner query must be processed first so that the result set can be passed on the main query.
Like combined queries, subqueries are always built in the Query panel.
You construct a subquery by placing a query filter on one of the objects in the main query and then using the operand for that query filter to launch the subquery. The operator you include in the query filter determines the relationship between the data sets returned by the inner and outer queries.
Typically you use subqueries when:
-The value of the operand is unknown.
-The query filter for the report involves a value that will change over time.
Now you will explore how and when to use subqueries.
Suppose that you have been asked by the Manager of the Chicago 33rd store of the eFashion Group to produce a report that lists stores and their revenue whenever those stores' revenue are higher than Chicago 33rd.
To produce the requested report, you need to resolve an issue regarding the query filter. The query filter for the report involves a value that is not known prior to the query being made, and it will change over time. Since the revenue of the Chicago 33rd store changes over time, you cannot enter a hard-coded figure for the sales revenue because the user would never be able to refresh the report and get accurate results.
To resolve this problem, you can use a subquery. Begin by creating a query using the Store name and Sales revenue objects, and then, using the Add a subquery button, create a subquery.
By default, the Sales revenue object appears in the subquery definition in the Query Filters pane. Since you want your report to display the results for all stores with sales revenue greater than or equal to the Chicago 33rd location, change the operand in the subquery to Greater than or Equal to. Then, complete the subquery by dragging the Store name dimension just below the subquery filter definition, and use this syntax:
Store name Equal to e-Fashion Chicago 33rd
When you run the query, the report returns the results for the Chicago 33rd store, as well as results for all the other stores with equal or greater sales revenue.
To build a subquery
Add the objects that you want to appear in the query to the Result Objects pane. 2. Select the object that you want to filter with a subquery.
Click Add a subquery.
The Add a subquery button appears in the toolbar for the Query Filters pane.
The subquery outline appears in the Query Filters pane. By default the object you selected appears as the Filter object and Filter By object.
To add a WHERE condition to the subquery, drag an object to the To filter the query... area. 5. Select the operator and values used to filter the object in the WHERE condition.
You can use a standard query filter as a WHERE condition in a subquery. To do so, drag and drop the existing filter or subquery to the To filter the query... area. To copy rather than move the existing filter to the WHERE condition, hold down the Control key while dragging and dropping. In this case the existing filter remains in its initial place and becomes part of the WHERE condition of the subquery.
In addition to linking subqueries in AND or OR relationships, you can nest them (create subqueries within subqueries) by dragging an existing subquery to the To filter the query...area. In this case the inner subquery becomes part of the WHERE condition of the outer subquery. To copy rather than move the subquery to the WHERE condition, hold down the Ctrl key while dragging and dropping. In this case the second subquery remains at the same level as the first, and becomes part of the WHERE clause of the first.
By default the two subqueries are linked in an AND relationship. Click the AND operator to toggle between AND and OR.
Activity: Using subqueries
Objective
Create a subquery using the results of one query as the starting point for a second query.
Instructions
You have been asked by the Manager of the Colorado Springs store of the eFashion Group to produce a report that lists product lines and their Margin, when the lines' Margin is higher than that of the Sweaters line.
Using the eFashion universe, create a new document and build a query using Line and
Margin.
Using the subquery technique, return those lines with a higher Margin than the Sweaters line.
You also need to display a title.
Save the document as Act_Subqueries.
Instructions
You have been asked by the Manager of the Colorado Springs store of the eFashion Group to produce a report that lists product lines and their Margin, when the lines' Margin is higher than that of the Sweaters line.
Using the eFashion universe, create a new document and build a query using Line and
Margin.
Using the subquery technique, return those lines with a higher Margin than the Sweaters line.
You also need to display a title.
Save the document as Act_Subqueries.
Creating a query based on another query
Web Intelligence allows you to create a query based on the structure of another query. Consequently, you save time with complex reports and can ensure that different queries are defined in exactly the same way.
After completing this unit, you will be able to:
• Duplicate a query in a document to build another query.
Duplicating queries
When you build a report with many queries based on the same universe, duplicating your query can accelerate the process, especially when the queries have some of the objects in common.
Suppose that you are asked to create a report for eFashion that displays state, year, and sales revenue. The report must use a prompted query filter to allow users to select the state for which to return the results.
To create the query, you place the State, Year, and Sales revenue objects in the Result Objects pane of the Query panel, and place the State object in the Query Filters pane, using the required prompt.
Web Intelligence allows you to create a query based on the structure of another query. Consequently, you save time with complex reports and can ensure that different queries are defined in exactly the same way.
After completing this unit, you will be able to:
• Duplicate a query in a document to build another query.
Duplicating queries
When you build a report with many queries based on the same universe, duplicating your query can accelerate the process, especially when the queries have some of the objects in common.
Suppose that you are asked to create a report for eFashion that displays state, year, and sales revenue. The report must use a prompted query filter to allow users to select the state for which to return the results.
To create the query, you place the State, Year, and Sales revenue objects in the Result Objects pane of the Query panel, and place the State object in the Query Filters pane, using the required prompt.
Next, suppose that you are asked to generate another report that prompts for state, but that this time displays sales revenue broken down by store and year, rather than by state and year. The query you need to create this report is almost identical to that required for the previous report. The only difference is that in the second query, you must use the Store name object in the Result Objects pane, where in the first query you used the State object.
To save yourself the effort of creating an entirely new query to generate the second report, you can duplicate the first query, delete the State object in the Result Objects pane, and replace it with the Store name object. When you run the new query, it produces the results you require.
To duplicate a query
Select the query you want to duplicate by right-clicking the appropriate Query tab at the bottom of the Query panel.
Select Duplicate Query.
A duplicate query appears in the Query panel, and its tab is labeled <query name> (1).
You can now modify the query.
Changing data sources
Web Intelligence allows you to change the data source used to create a document:
From one universe to another universe
From a local data source to a universe
You cannot change data sources from a universe to a local data source.
After completing this unit, you will be able to:
Change the data source for a document
Map the objects to the new data source
About changing data sources
With the Web Intelligence query interface, you can change the data source for your query when required.
This is particularly useful when:
You move a test universe into production and want to use the same documents on the new universe. For example, you can change the test universe stores to the production universe eFashion.
You change between different universes covering different functional areas but based on the same database.
For example, an organization has a Marketing universe and a Sales universe based on the Sales database. The Marketing universe uses objects for companies, contacts, leads, and promotional efforts. The Sales universe uses objects for companies, contacts, sales orders, and sales revenue.
The Marketing managers want to know how many contacts established during a promotional campaign actually led to sales within the six months following the campaign.
With appropriate access to the Sales universe, the managers can take a query that retrieves the companies and contacts that participated in the campaign, change the universe from Marketing to Sales, add the Sales Revenue object from the Sales universe to the query and filter to retrieve data for the six-month period following the campaign.
To change the data source
From your document in the Web Intelligence Rich Client main window, click the Edit Query button. The Query panel displays.
For a document built on a local data source, the data source appears in the Query Properties section of the left-hand panel.
For a document built on a universe, click the Properties tab. The Universe field appears on the Properties tab.
To change the data source:
For a document built on a universe, next to the Universe field, click the … (ellipsis) button.
The Universe dialog box displays with a list of the universes that you can use to replace the current one.
For a document built on a local data provider, next to the Source file field, click the … (ellipsis) button.
The Choose File dialog box displays for you to browse to a new local data source.
To select the new data source:
- In the Universe dialog box, click to select the universe and click the OK button.
-In the Choose File dialog box, browse to select the local data source and click the Open button.
The Change Source dialog box displays to show you how the objects from the query on the original data source map to objects in the new data source. The first column shows the object from the original query, or the source object, and the second column shows the object in the new data source, or the target object.
Web Intelligence proposes mapping to objects based on the object name and data type.
-The check mark to the left of the object names indicates that the object is mapped to another object in the new data source.
-The X to the left of the object name indicates that the object is removed from the query on the new data source.
-The check mark to the left of the object names indicates that the object is mapped to another object in the new data source.
-The X to the left of the object name indicates that the object is removed from the query on the new data source.
Optionally, to modify the proposed mapping or removal of an object, click the ... (ellipsis) button on the line corresponding to the object for which you want to modify the mapping.
The Map Object dialog box displays.
In the Map Object dialog box:
• To remove the object, select the Remove object option .
• To map to a new object, select the Select a new object option and click the object from
the new data source displayed in the Map Object dialog box.
Click OK to close the Map Object dialog box.
Repeat steps 5 and 6 for each object with a proposed mapping or removal that you want to change.
Click OK to close the Change Source dialog box.
In the Query panel, in the Properties tab, the Universe field shows the new data source.
The Result Objects pane shows the objects for the new data source.
Click Run Query.
The results of the query on the new data source display in the Web Intelligence Rich Client main window.
Save the document
Activity: Changing the data source
Objective
In this activity, you change the data source from the development universe (stores) to the production universe (eFashion).
Instructions
From the course resources, open the Sales per Region.wid document. This document was created using the stores universe. The report looks like this:
Change the data source to the eFashion universe and map the objects as appropriate.
To achieve the desired results, remove one object from the query and map another object to a corresponding object in the new universe that has a different name.
For indepth understanding click on
You liked the article?
Like: 0
Vote for difficulty
Current difficulty (Avg): Medium
TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.
